package org.example.practiceexamples.repository;

import org.example.practiceexamples.dox.Course;
import org.example.practiceexamples.dto.CourseAppointment;
import org.example.practiceexamples.mapper.CourseAppointmentResultSetExtractor;
import org.springframework.data.jdbc.repository.query.Modifying;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import javax.sql.rowset.CachedRowSet;
import java.util.List;

@Repository
public interface CourseRepository extends CrudRepository<Course, String> {


    @Query("""
        select id from course where name = :name and teacher_id =:tid and class_number = :classNum
    """)
    String findId(String name, String tid, String classNum);

    @Query("""
        select * from course
    """)
    List<Course> findAllCourses();

    @Query("select * from course where teacher_id =:tid")
    List<Course> findCourseByTeacherId(String tid);

    @Query("""
         select * from course where name = :name and teacher_id =:tid and class_number = :classNum
    """)
    Course findCourseByNameAndTeacherId(String name, String tid, Integer classNum);

    @Query("""
        select * from course where id =:cid
    """)
    Course findCourseById(String cid);

    @Query("""
        select course.experimental_hours from course where id = :cid
    """)
    int findExpermentalHoursById(String cid);

    @Query("select course.lesson from course where id =:cid")
    int findLessonById(String cid);

    @Query("""
        select course.theoretical_hours from course where id = :cid
    """)
    int findTheoreticalHoursById(String cid);

    @Query("""
        select quantity  from course where id =:cid
    """)
    int findQuantityById(String cid);

    @Query("""
        select course.class_number from course where id = :cid
    """)
    String findClassNumberById(String cid);


    @Modifying
    @Query("""
        delete from course where id = :cid 
    """)
    void deleteById(String cid);

    @Modifying
    @Query("""
        update course set quantity = :newQuantiry where id = :cid
    """)
    void updateQuantity(String cid, int newQuantiry);


    @Query(value = """
        select * from course c join appointment a on c.id=a.course->>'$.id'  where c.id = :cid
    """,resultSetExtractorClass = CourseAppointmentResultSetExtractor.class)
    CourseAppointment findCourseAppointmentById(String cid);
}
